﻿Imports System.Data.OleDb

Public Class AddComments
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        If IsPostBack = False Then

            If Request.Params("ID") = "" Then Response.Redirect("PhoneList.aspx")
            Dim conn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("PhonesConn").ConnectionString)
            Dim SqlString As String = "SELECT * FROM Comments WHERE Phone_ID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, conn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            conn.Open()

            Dim PhonesViewed As String = "INSERT INTO PhonesViewed (Adding_User,Phonefkid) VALUES (@f1,@f2)"
            Dim SelectAll As String = "SELECT (ID) FROM Phones VALUES (@f3)"
            Dim cmd2 = New OleDbCommand(PhonesViewed, conn)

            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f3", Request.Params("ID"))
            cmd2.ExecuteNonQuery()

        End If


        Dim sql5 As String = "SELECT * FROM AverageRating WHERE PhoneFkID=@f1"
        Dim conn5 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("PhonesConn").ConnectionString)
        conn5.Open()
        Dim cmd5 As OleDbCommand = New OleDbCommand(sql5, conn5)
        cmd5.Parameters.AddWithValue("@f1", Request.QueryString("id"))
        Dim dataReader = cmd5.ExecuteReader
        dataReader.Read()
        Try
            If Not IsDBNull(dataReader("AverageRating")) Then lblAverageRatings.Text = dataReader("AverageRating")
        Catch ex As Exception
            lblAverageRatings.Text = 0
        End Try

        Dim int As Single = CInt(lblAverageRatings.Text)

    End Sub

    Protected Sub btn_Save_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Save.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("PhonesConn").ConnectionString)
        Dim sql = "INSERT INTO COMMENTS (Author,Subject,Message,Comment_Date,PhoneID) VALUES (@f1,@f2,@f3,@f4,@f5)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_Subject.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Message.Text)
        cmd.Parameters.AddWithValue("@f4", Now.ToString)
        cmd.Parameters.AddWithValue("@f5", Request.Params("ID"))
        cmd.ExecuteNonQuery()

        conn.Close()
        Response.Redirect("AddComments.aspx?ID=" & Request.Params("ID"))



    End Sub

    Protected Sub btn_AddTag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddTag.Click
        Dim phoneID = Request.Params("ID")
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("PhonesConn").ConnectionString)
        conn.Open()

        Dim newTags = tb_AddTag.Text.Split(",")
        For Each newTag As String In newTags
            Dim isTagThereCmd As New OleDbCommand(" SELECT * FROM Tags WHERE TagName=@f1", conn)
            isTagThereCmd.Parameters.AddWithValue("@f1", newTag)
            Dim dr = isTagThereCmd.ExecuteReader()
            If Not dr.HasRows Then
                Dim addTagCmd As New OleDbCommand(" INSERT INTO Tags (TagName) VALUES (@f1)", conn)
                addTagCmd.Parameters.AddWithValue("@f1", newTag)
                addTagCmd.ExecuteNonQuery()
            End If

            Dim findNewTagIDCmd As New OleDbCommand("SELECT TagID FROM Tags WHERE TagName=@f1", conn)
            findNewTagIDCmd.Parameters.AddWithValue("@f1", newTag)

            Dim newTagDr = findNewTagIDCmd.ExecuteReader()
            newTagDr.Read()

            Dim newID = newTagDr.Item("TagID")
            Dim makeAssosiationCmd As New OleDbCommand(" INSERT INTO PhoneTagJunction (Tagfkid,Phonefkid) VALUES (@f1,@f2)", conn)
            makeAssosiationCmd.Parameters.AddWithValue("@f1", newID)
            makeAssosiationCmd.Parameters.AddWithValue("@f2", phoneID)

            makeAssosiationCmd.ExecuteNonQuery()

        Next

        For Each li As ListItem In cblTags.Items
            If (li.Selected = True) Then
                Dim sql = "INSERT INTO PhoneTagJunction (Tagfkid,Phonefkid) VALUES (@f1,@f2)"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@f1", li.Value)
                cmd.Parameters.AddWithValue("@f2", phoneID)
                cmd.ExecuteNonQuery()
            End If

        Next
        conn.Close()


    End Sub

    Protected Sub btn_SaveRating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_SaveRating.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("PhonesConn").ConnectionString)

        Dim rmsql = "DELETE FROM RATING WHERE Rater=@f1 AND PhoneFkID=@f2"
        Dim rmcmd As New OleDbCommand(rmsql, conn)
        conn.Open()
        rmcmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        rmcmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        rmcmd.ExecuteNonQuery()
        conn.Close()


        Dim sql = "INSERT INTO RATING (Rating,PhoneFkID,Rater) VALUES (@f1,@f2,@f3)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Rating.CurrentRating)
        cmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()


    End Sub

   


End Class